package org.charmsoft.validation;

import cn.hutool.core.io.FileUtil;
import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.charmsoft.utils.ExcelUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;
import java.util.regex.Pattern;

/**
 * @ Author: Allen J L Chen
 * This Class is to
 * 13/4/2025
 **/
public class ValidationUtils {

    private static final Pattern pojoNamePattern = Pattern.compile(ExcelTemplateFormat.POJO_NAME_FORMAT.getValue());

    private static final Pattern fieldNamePattern = Pattern.compile(ExcelTemplateFormat.FIELD_NAME_FORMAT.getValue());

    private static final Pattern fieldTypePattern = Pattern.compile(ExcelTemplateFormat.FIELD_TYPE_NAME_FORMAT.getValue());

    private static final Pattern sqlDatatypePattern = Pattern.compile(ExcelTemplateFormat.SQL_DATA_TYPE_FORMAT.getValue());

    private static final Logger LOGGER = LoggerFactory.getLogger(ValidationUtils.class);

    private static final Path path = Paths.get("");

    private static final String IN_CORRECT_VALUE = " sheet is invalid. Please fill correct value.";

    private static final String EMPTY_SHEET = " sheet is empty. Please fill correct value or delete it.";

    private static final short elegantBlue = IndexedColors.LIGHT_BLUE.getIndex();
    private static final short elegantGreen = IndexedColors.LIGHT_GREEN.getIndex();

    public static ValidationWrapper validateWorkbook(Map<String, XSSFWorkbook> workbookMap) {
        ValidationWrapper validationWrapper = new ValidationWrapper();

        Map<String, XSSFWorkbook> validWorkbookMap = validationWrapper.getValidWorkbookMap();
        Map<String, Map<String, XSSFWorkbook>> invalidWorkbookMap = validationWrapper.getInvalidWorkbookMap();

        workbookMap.forEach((name, workbook)  -> {
            Font font = workbook.createFont();
            font.setColor(Font.COLOR_RED);
            validateHeaderLine(workbook, validationWrapper, font, name);
            validateValueFormat(workbook,validationWrapper, font, name);
            if(!validationWrapper.getInvalidWorkbookMap().containsKey(name)){
                validWorkbookMap.putIfAbsent(name, (XSSFWorkbook) workbook);
            }
        });

        invalidWorkbookMap.forEach((name, innerWorkbookMap) -> {
            String sPath = path.toAbsolutePath().toString()+"/src/main/resources/excel/";
            AtomicReference<String> errorMessage = new AtomicReference<>();
            if(!FileUtil.isDirectory(sPath)){
                FileUtil.mkdir(sPath);
            }

            try ( OutputStream ops = new FileOutputStream(sPath + name);){


                innerWorkbookMap.forEach((message, workbook)->{
                    errorMessage.set(message);
                    try {
                        workbook.write(ops);
                    } catch (IOException e) {
                        throw new RuntimeException(e);
                    }
                });

            } catch (IOException e) {
                LOGGER.error(e.getMessage());
            } finally {
                LOGGER.error("\n\n" +"*********************************************************\n" +
                        name + " validation is fail. \n" + errorMessage.get()+"\n" +
                        "*********************************************************\n");
            }
        });
        return validationWrapper;
    }

    public static void  validateHeaderLine(XSSFWorkbook workbook, ValidationWrapper validationWrapper, Font font, String name) {
        Map<String, Map<String, XSSFWorkbook>> invalidWorkbookMap = validationWrapper.getInvalidWorkbookMap();
        Map<Cell, ValidationErrorCode> validationMap = validationWrapper.getValidationMap();

        for(int i =0; i< workbook.getNumberOfSheets(); i++) {
            boolean isSheetLocked = workbook.getSheetAt(i).isSheetLocked();
            Sheet sheet = workbook.getSheetAt(i);

            if(!isSheetLocked) {
                validateHeaderLineWithoutLockedSheet(workbook, sheet, invalidWorkbookMap, validationMap,font,name);
            }

        }

    }

    public static void validateHeaderLineWithoutLockedSheet(XSSFWorkbook workbook, Sheet sheet, Map<String, Map<String,XSSFWorkbook>> invalidWorkbookMap, Map<Cell, ValidationErrorCode> validationMap, Font font, String name){
        // Validate pojo name headline.
        Row firstRow = sheet.getRow(sheet.getFirstRowNum());
        if (firstRow != null) {
            Map<Integer, Cell> pojoNameHeadLineCells = ExcelUtils.loopCellBySkipOneColumn(firstRow, 0);
            pojoNameHeadLineCells.forEach((index, cell) -> {
                String pojoNameHeadLineValue = cell.getStringCellValue();
//                    LOGGER.info(ExcelTemplateFormat.POJO_HEAD_LINE.getValue());
                LOGGER.info("Foreground Color: " + cell.getCellStyle().getFillForegroundColor());
                LOGGER.info("Background Color: " + cell.getCellStyle().getFillBackgroundColor());

                if (pojoNameHeadLineValue.isEmpty()) {
                    Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                    xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                    invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                    ValidationUtils.setRedColorFont(workbook, cell);
                    cell.setCellValue(ValidationErrorCode.POJO_HEAD_LINE_INVALID.getValue());
                    validationMap.putIfAbsent(cell, ValidationErrorCode.POJO_HEAD_LINE_INVALID);

                } else if(!pojoNameHeadLineValue.equals(ExcelTemplateFormat.POJO_HEAD_LINE.getValue())) {
                    LOGGER.info(cell.getStringCellValue());
                    ValidationUtils.setRedColorFont(workbook, cell);
                    Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                    xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                    invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                } else {
                    ValidationUtils.setElegantBlue(workbook, cell);
                    ValidationUtils.setBlackColorFont(workbook, cell);
                }

            });
        } else {
            Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
            xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + EMPTY_SHEET, (XSSFWorkbook) sheet.getWorkbook());
            invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
        }

        Row thirdRow = sheet.getRow(2);
        if (thirdRow != null) {
            // Validate fields headline.
            Map<Integer, Cell> fieldsHeadLineCells = ExcelUtils.loopCellBySkipOneColumn(thirdRow, 0);
            System.out.println(fieldsHeadLineCells.size());
            fieldsHeadLineCells.forEach((index, cell) -> {
                String fieldsHeadLineValue = cell.getStringCellValue();
                if (fieldsHeadLineValue.isEmpty()) {
                    Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                    xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                    invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                    cell.setCellValue(ValidationErrorCode.FIELDS_HEAD_LINE_INVALID.getValue());
                    ValidationUtils.setRedColorFont(workbook, cell);
                    validationMap.putIfAbsent(cell, ValidationErrorCode.FIELDS_HEAD_LINE_INVALID);
                }else if(!fieldsHeadLineValue.equals(ExcelTemplateFormat.FIELDS_HEAD_LINE.getValue())) {
                    ValidationUtils.setElegantGreen(workbook, cell);
                    ValidationUtils.setRedColorFont(workbook, cell);
                } else {
                    ValidationUtils.setElegantBlue(workbook, cell);
                    ValidationUtils.setBlackColorFont(workbook, cell);
                }
            });

            // Validate field datatype headline.
            Map<Integer, Cell> fieldDatatypeHeadLineCells = ExcelUtils.loopCellBySkipOneColumn(thirdRow, 1);
            fieldDatatypeHeadLineCells.forEach((index, cell) -> {
                String fieldDatatypeHeadLineValue = cell.getStringCellValue();
                if (fieldDatatypeHeadLineValue.isEmpty()) {
                    Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                    xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                    invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                    ValidationUtils.setRedColorFont(workbook, cell);
                    cell.setCellValue(ValidationErrorCode.FIELDS_DATATYPE_HEAD_LINE_INVALID.getValue());

                    validationMap.putIfAbsent(cell, ValidationErrorCode.FIELDS_DATATYPE_HEAD_LINE_INVALID);
                } else if(!fieldDatatypeHeadLineValue.equals(ExcelTemplateFormat.FIELD_DATATYPE_HEAD_LINE.getValue())) {
                    ValidationUtils.setRedColorFont(workbook,cell);
                } else {
                    ValidationUtils.setElegantBlue(workbook, cell);
                    ValidationUtils.setBlackColorFont(workbook, cell);
                }
            });

            // Validate SQL datatype headline.
            Map<Integer, Cell> sqlDatatypeHeadLineCells = ExcelUtils.loopCellBySkipOneColumn(thirdRow, 2);
            sqlDatatypeHeadLineCells.forEach((index, cell) -> {
                String sqlDatatypeHeadLineValue = cell.getStringCellValue();
                if (sqlDatatypeHeadLineValue.isEmpty()) {
                    Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                    xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                    invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                    ValidationUtils.setRedColorFont(workbook, cell);
                    cell.setCellValue(ValidationErrorCode.SQL_DATATYPE_HEAD_LINE_INVALID.getValue());
                    validationMap.putIfAbsent(cell, ValidationErrorCode.SQL_DATATYPE_HEAD_LINE_INVALID);
                } else if(!sqlDatatypeHeadLineValue.equals(ExcelTemplateFormat.SQL_DATATYPE_HEAD_LINE.getValue())) {
                    ValidationUtils.setRedColorFont(workbook,cell);
                } else {
                    ValidationUtils.setElegantBlue(workbook, cell);
                    ValidationUtils.setBlackColorFont(workbook, cell);
                }
            });
        } else {
            Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
            xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
            invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
        }
    }

    public static void validateValueFormat(Workbook workbook, ValidationWrapper validationWrapper, Font font, String name) {
        Map<String, Map<String, XSSFWorkbook>> invalidWorkbookMap = validationWrapper.getInvalidWorkbookMap();
        Map<Cell, ValidationErrorCode> validationMap = validationWrapper.getValidationMap();

        for (int i = 0; i < workbook.getNumberOfSheets()-1; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int rowCount = sheet.getPhysicalNumberOfRows();
            // Validate pojo name format.
            Row secondRow = sheet.getRow(1);
            if (secondRow != null) {
                Map<Integer, Cell> pojoNameCells = ExcelUtils.loopCellBySkipOneColumn(secondRow, 0);
                pojoNameCells.forEach((index, cell) -> {
                    String pojoName = cell.getStringCellValue();
//                    LOGGER.info("pojoName matches:" + pojoNamePattern.matcher(pojoName).matches());
                    if (!pojoNamePattern.matcher(pojoName).matches() || pojoName.isEmpty()) {
                        Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                        xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                        invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                        ValidationUtils.setRedColorFont(workbook, cell);
                        if(pojoName.isEmpty()) {
                            cell.setCellValue(ValidationErrorCode.POJO_NAME_FORMAT_INVALID.getValue());
                        } else {
                            ValidationUtils.setRedColorFont(workbook, cell);
                        }
                        validationMap.putIfAbsent(cell, ValidationErrorCode.POJO_NAME_FORMAT_INVALID);
                    }
                });
            } else {
                Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
            }

            for (int j = 3; j <= rowCount; j++) {
                Row row = sheet.getRow(j);
                if (row != null) {
                    // Validate field name format.
                    Map<Integer, Cell> fieldNameCells = ExcelUtils.loopCellBySkipOneColumn(row, 0);
                    fieldNameCells.forEach((index, cell) -> {
                        String fieldName = cell.getStringCellValue();
                        if (!fieldNamePattern.matcher(fieldName).matches() || fieldName.isEmpty()) {
                            Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                            xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                            invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                            ValidationUtils.setRedColorFont(workbook, cell);
                            if(fieldName.isEmpty()) {
                                cell.setCellValue(ValidationErrorCode.FIELD_NAME_FORMAT_INVALID.getValue());
                            } else {
                                ValidationUtils.setRedColorFont(workbook,cell);
                                ValidationUtils.setElegantGreen(workbook,cell);
                            }
                            validationMap.putIfAbsent(cell, ValidationErrorCode.FIELD_NAME_FORMAT_INVALID);
                        }
                    });

                    // Validate field type.
                    Map<Integer, Cell> fieldTypeCells = ExcelUtils.loopCellBySkipOneColumn(row, 1);
                    fieldTypeCells.forEach((index, cell) -> {
                        String fieldType = cell.getStringCellValue();
                        if (!fieldTypePattern.matcher(fieldType).matches() || fieldType.isEmpty()) {
                            Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                            xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                            invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                            ValidationUtils.setRedColorFont(workbook, cell);
                            if(fieldType.isEmpty()) {
                                cell.setCellValue(ValidationErrorCode.FIELD_TYPE_FORMAT_INVALID.getValue());
                            } else {
                                ValidationUtils.setRedColorFont(workbook, cell);
                            }
                            validationMap.putIfAbsent(cell, ValidationErrorCode.FIELD_TYPE_FORMAT_INVALID);
                        }
                    });

                    // Validate SQL datatype.
                    Map<Integer, Cell> sqlDatatypeCells = ExcelUtils.loopCellBySkipOneColumn(row, 2);
                    sqlDatatypeCells.forEach((index, cell) -> {
                        String sqlDataType = cell.getStringCellValue();
                        if (!sqlDatatypePattern.matcher(sqlDataType).matches() || sqlDataType.isEmpty()) {
                            Map<String, XSSFWorkbook> xssfWorkbookMap = new HashedMap<>();
                            xssfWorkbookMap.putIfAbsent(sheet.getSheetName() + IN_CORRECT_VALUE, (XSSFWorkbook) sheet.getWorkbook());
                            invalidWorkbookMap.putIfAbsent(name, xssfWorkbookMap);
                            ValidationUtils.setRedColorFont(workbook, cell);
                            if(sqlDataType.isEmpty()) {
                                cell.setCellValue(ValidationErrorCode.SQL_DATATYPE_FORMAT_INVALID.getValue());
                            } else {
                                ValidationUtils.setRedColorFont(workbook,cell);
                            }
                            validationMap.putIfAbsent(cell, ValidationErrorCode.SQL_DATATYPE_FORMAT_INVALID);
                        }
                    });
                }
            }
        }
    }

    public static void setCellForegroundColor(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
    }

    public static void setCellBackgroundColor(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillBackgroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
    }

    public static void setElegantBlue(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(elegantBlue);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
    }

    public static void setElegantGreen(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(elegantGreen);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
    }

    public static void setRedColorFont(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        font.setBold(true);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    public static void setBlackColorFont(Workbook workbook, Cell cell) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setBold(true);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    public static boolean checkIfOpened(XSSFWorkbook workbook){
        return workbook.isRevisionLocked();
    }
}
